﻿--获取员工工资表
CREATE PROCEDURE [dbo].[proc_Employee_WageTable_GetList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@username nvarchar(50),
	@company nvarchar(4),           --单位
	@department  varchar(8000),      --部门
	@postcategory nvarchar(4),      --岗位类别
	@post nvarchar(4),		--岗位
	@grade nvarchar(4),             --职级 
	@name nvarchar(50)	        --姓名

)
as

--公司Id
declare @companyid nvarchar(4)
set @companyid = (select CompanyId from aspnet_Users where username=@username)


if(@companyid = '6')
	set @companyid =@company

declare @sql_Count  varchar(8000)
declare @sql_main   varchar(8000)

set nocount on
	if(@docount=1)
		begin
		set @sql_Count =
		+' select *,1 as CompanyName,1 as DepartmentName,1 as PostCategoryName,1 as PostName,1 as GradeName,0 as ChuQin,0 as BasicWage,0 as PostWage,0 as WorkWage,0 as JxWage,0 as ManQin,0 as Allowance,0 as TiCheng,0 as MTiCheng,0 as YjBonus,0 as LsBonus, 0 as bxKouKuan  from Employee' 
		+' where' 
			+'  CompanyId like '''+@companyid+''''
			+' and departmentid in ('+@department+')'
			+' and postcategory like '''+@postcategory+''''
			+' and postid like '''+@post+''''
			+' and GradeId like '''+@grade+''''
			+' and EName like ''%'+@name+'%'''
			+' and  state <> 5'
		print(@sql_Count)
		exec (@sql_Count)
		end
	else
	begin
	set @sql_main = 
	+ ' declare @indextable table(id int identity(1,1),nid int)'
	+ ' set rowcount '+ @endIndex
	+ ' insert into @indextable (nid)'
	+ ' select id'
	+ ' from Employee'
		+' where' 
		        +'  CompanyId like '''+@companyid+''''
			+' and departmentid in ('+@department+')'
			+' and postcategory like '''+@postcategory+''''
			+' and postid like '''+@post+''''
			+' and GradeId like '''+@grade+''''
			+' and EName like ''%'+@name+'%'''
			+' and  state <> 5'
	+' order by id asc'
	+' select'
		 +' E.id'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.EName'
		+' ,E.CompanyId'
		+' ,(select CompanyName from Company where id=E.CompanyId) as CompanyName'
		+' ,(isnull(E.DepartmentId,0)) as DepartmentId'
		+' ,(isnull((select DName from Department where id=E.DepartmentId),'''')) as DepartmentName'
		+' ,E.PostCategory'
		+' ,(isnull((select title from post_category where id=E.PostCategory),'''')) as PostCategoryName'
		+' ,E.PostId'
		+' ,(isnull((select PostName from Post where id=E.PostId),'''')) as PostName'
		+' ,E.GradeId'
		+' ,(isnull((select Title from Grade where id=E.GradeId),'''')) as GradeName'
		+' ,26 as ChuQin'
		+' ,(isnull((select Wage from Grade where id=E.GradeId),0)) as BasicWage'
		+' ,(isnull((select PostWage from Post where id=E.PostId),0)) as PostWage'
		+' ,(isnull((datediff(dd,ruzhidate,getdate())/360*100),0)) as WorkWage'
		+' ,(isnull((select JxWage from Post where id=E.PostId),0)) as JxWage'
		+' ,(isnull((select ManQin from Post where id=E.PostId),0)) as ManQin'
		+' ,(isnull((select amount from Employee_Allowance EA where id=(select Allowance from post p where p.id=E.PostId)),0)) as Allowance'
		+' ,0 as TiCheng'
		+' ,0 as MTiCheng'
		+' ,0 as YjBonus'
		+' ,0 as LsBonus'
		+' ,0 as bxKouKuan'

	+' from Employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'
	+' where' 
		+' t.id between '+ @startIndex +' and '+ @endIndex 
	+' order by t.id'

	print(@sql_main)
	exec (@sql_main)
	end
set nocount off
RETURN